"""
Case Type   : GUC
Case Name   : 并发触发自动淘汰，查看记录条数
Create At   : 2021/05/08
Owner       : opentestcase018
Description :
    1、查询参数默认值；
    show enable_auto_clean_unique_sql;
    show enable_resource_track;
    show instr_unique_sql_count;
    2、修改enable_auto_clean_unique_sql为on，重启使其生效，并校验其预期结果；
    gs_guc set -N all -I all -c "enable_auto_clean_unique_sql=on"
    gs_om -t stop && gs_om -t start
    3、清空记录，执行不足100 unique_sql，查看hash table记录条数
    select reset_unique_sql('GLOBAL','ALL',100);
    select count(va) from (select get_instr_unique_sql() as va);
    gsql -r -d -p -f test.sql
    select count(va) from (select get_instr_unique_sql() as va);
    4、并发9线程执行1 unique_sql，触发自动淘汰
    5、恢复默认值；
    gs_guc set -N all -I all -c "enable_auto_clean_unique_sql=off"
    gs_om -t stop && gs_om -t start
Expect      :
    1、显示默认值；
    2、参数修改成功，校验修改后系统参数
    3、不触发自动淘汰，查看hash table记录条数小于等于100，大于等于94
    4、9线程执行成功 未有长时间卡顿
    5、恢复默认值成功
History     :
    modified by opentestcase018 at 2021-10-26:优化用例，删除参数，优化断言
    modified by opentestcase013 at 2022/09/27:优化用例，修改SQL语句循环数量，
                                         使其在预期阶段触发自动淘汰
    modified by opentestcase013 at 2022/11/03:优化用例
    modified by opentestcase013 at 2023/02/21:数据库内部自动执行了8个语句，优化原断言信息
"""

import os
import unittest

from testcase.utils.ComThread import ComThread
from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant
from testcase.utils.Logger import Logger

LOGGER = Logger()
COMMONSH = CommonSH("PrimaryDbUser")


class Guctestcase(unittest.TestCase):
    def setUp(self):
        LOGGER.info(f'-----{os.path.basename(__file__)} start-----')

        self.constant = Constant()
        status = COMMONSH.get_db_cluster_status("detail")
        LOGGER.info(status)
        self.assertTrue("Normal" in status or "Degraded" in status)
        status = COMMONSH.restart_db_cluster()
        LOGGER.info(status)
        status = COMMONSH.get_db_cluster_status("detail")
        LOGGER.info(status)
        self.assertTrue("Normal" in status or "Degraded" in status)

    def set_gs_guc(self, param, value, method="set"):
        sql_cmd = COMMONSH.execut_db_sql(f"show {param}")
        LOGGER.info(sql_cmd)
        if f"{value}" != sql_cmd.splitlines()[-2].strip():
            COMMONSH.execute_gsguc(method,
                                   self.constant.GSGUC_SUCCESS_MSG,
                                   f"{param}={value}")
        LOGGER.info(f"{param} is {value}")

    def test_unique_sql(self):
        LOGGER.info("步骤1+2：核对参数默认值 并修改参数")
        self.set_gs_guc("enable_auto_clean_unique_sql", "on")
        status = COMMONSH.restart_db_cluster()
        LOGGER.info(status)
        self.set_gs_guc("enable_resource_track", "on", "reload")
        self.set_gs_guc("instr_unique_sql_count", "100", "reload")
        sql_cmd = COMMONSH.execut_db_sql("show enable_resource_track;"
                                         "show instr_unique_sql_count;"
                                         "show enable_auto_clean_unique_sql;")
        LOGGER.info(sql_cmd)
        self.assertNotIn("off", sql_cmd)
        self.assertIn("on", sql_cmd)
        self.assertIn("100", sql_cmd)

        LOGGER.info("步骤3：清空记录后执行90不触发自动淘汰")
        LOGGER.info(COMMONSH.node.db_name)
        result = COMMONSH.execut_db_sql("select "
            "reset_unique_sql('GLOBAL','ALL',100);"
            "select count(*) from dbe_perf.statement;")
        LOGGER.info(result)
        self.assertIn("t\n", result)
        for j in range(44):
            tablename = f"unique_table_{j}"
            result = COMMONSH.execut_db_sql(f"drop table if exists "
                f"{tablename};create table {tablename}(a int, b int);"
                f"drop table if exists {tablename};")
            LOGGER.info(result)
            self.assertNotIn("ERROR", result)
        sql_detail = COMMONSH.execut_db_sql("select user_id,"
                                            "unique_sql_id,"
                                            "query from dbe_perf."
                                            "statement;")
        LOGGER.info(sql_detail)
        result = COMMONSH.execut_db_sql("select count(*) "
            "from dbe_perf.statement;")
        LOGGER.info(result)
        self.assertLessEqual(len(result.splitlines()), 100)
        new_res = int(result.splitlines()[-2])
        self.assertLessEqual(new_res, 100)
        self.assertGreaterEqual(new_res, 90)

        LOGGER.info("步骤4：并发15线程执行sql触发自动淘汰")
        connect_thread = []
        for i in range(15):
            sql_cmd = "select count(va) from " \
                      "(select get_instr_unique_sql() as va);"
            connect_thread.append(ComThread(COMMONSH.execut_db_sql,
                                            args=(sql_cmd, "")))
            connect_thread[i].setDaemon(True)
            connect_thread[i].start()
        for i in range(15):
            connect_thread[i].join(1)
            result = connect_thread[i].get_result()
            LOGGER.info(result)
        new_res = int(result.splitlines()[-2])
        self.assertLessEqual(new_res, 99)

    def tearDown(self):
        LOGGER.info("步骤5：恢复默认值")
        self.set_gs_guc("enable_auto_clean_unique_sql", "off")
        status = COMMONSH.restart_db_cluster()
        LOGGER.info(status)
        status = COMMONSH.get_db_cluster_status("detail")
        LOGGER.info(status)
        self.assertTrue("Normal" in status or "Degraded" in status)
        LOGGER.info(f'-----{os.path.basename(__file__)} end-----')
